SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC [dbo].[SP_TASALD_CORP_Q25]
/*---------------------------------------------------------------------*/
/*----- Empresa          : OFISIS S.A.                            -----*/
/*----- Cliente          : OFISIS S.A.                         	  -----*/
/*----- Sistema          : Contabilidad                           -----*/
/*----- Modulo           : Corporacion                            -----*/
/*----- Programa         : Balance de Sumas y Saldos Corporativo(CON QUIEBRE)  -----*/
/*----- Nombre           : SP_TASALD_CORP_Q25              	  -----*/
/*----- Script           : TASALD_CORP                            -----*/
/*----- Desarrollado     : Franco Gamboni Koifman                 -----*/
/*----- Fecha            : 24/10/1997                             -----*/
/*----- Base de Datos    : MS SQL Server                          -----*/
/*----- Version BD       : 6.5                                    -----*/
/*----- Invoca a SP      :                                        -----*/
/*---------------------------------------------------------------------*/
/*---------------------------------------------------------------------*/
/*----- Modificado 1 por : Martin Garcia Farfan                   -----*/
/*----- Fecha Modifica   : 12/03/1999                             -----*/
/*----- Detalle Modifica : Estandarizacion                        -----*/
/*---------------------------------------------------------------------*/
/*---------------------------------------------------------------------*/
/*----- MODIFICADO 2 POR : PERCY GARCIA                           -----*/
/*----- FECHA MODIF      : 06/12/2004                             -----*/
/*----- DETALLE MODIF    : SE AUMENTO FILTROS PARA VISUALIZAR FILAS CON MONTOS -----*/
/*---------------------------------------------------------------------*/
/*---------------------------------------------------------------------*/
/*----- Drop Proc SP_TASALD_CORP_Q25                            -----*/
/*----- Grant Execute On SP_TASALD_CORP_Q25 To PUBLIC             -----*/
/*----- SP_TASALD_CORP_Q25 2002 ,08, 5                	  -----*/
/*---------------------------------------------------------------------*/
@INNU_ANNO	  	TD_IN_001,
@INNU_MESE	  	TD_IN_001,
@INNU_DGTO        	TD_IN_001,
@ISTI_PRES		TD_VC_001,
@ISFL_MONT		TD_VC_001


As
Declare
 @CSCO_CNTA	  	TD_VC_010,
 @CSNO_CNTA	  	TD_VC_050,
 @CNIM_DEBE	  	TD_NU_016_004,
 @CNIM_HABE	  	TD_NU_016_004,
 @VNIM_DEBE_ACUM	TD_NU_016_004,
 @VNIM_HABE_ACUM  	TD_NU_016_004,
 @VSDE_CMDO		Char(255) 
 Create Table #TWSALD_CORP_Q01_1
 (  CO_CNTA_CORP Varchar(10), 
    DE_CNTA_CORP Varchar(50),
    DEBE	 Numeric(16,4),
    HABER  	 Numeric(16,4),
    DEBE_ACUM    Numeric(16,4),
    HABER_ACUM   Numeric(16,4))

If @INNU_DGTO = 0
 Begin
    Declare CU_TASALD_CORP_Q01_1 cursor
    For	
     Select  
		CUENTA      = T2.CO_CNTA_CORP, NOMBRE = MAX(RTRIM(T2.DE_CNTA_CORP)),
      	CARGO_SOLES = ISNULL(SUM(ROUND(ISNULL(T1.IM_CARG_MESE, 0),2)), 0), 
      	ABONO_SOLES = ISNULL(SUM(ROUND(ISNULL(T1.IM_ABON_MESE, 0),2)), 0)
     From TMPLAN_CORP T2
       Left Outer Join TASALD_CORP T1
	   On T1.NU_ANNO	= @INNU_ANNO 
		And T1.NU_MESE	= @INNU_MESE 
		And T1.CO_CNTA_CORP = T2.CO_CNTA_CORP 
		And T1.TI_AUXI_CORP IS NULL
     Group by T2.CO_CNTA_CORP
End 
If @INNU_DGTO != 0
 Begin
    Declare CU_TASALD_CORP_Q01_1 cursor
    For	
     Select  
		CUENTA      = T2.CO_CNTA_CORP, NOMBRE = MAX(RTRIM(T2.DE_CNTA_CORP)),
      	CARGO_SOLES = ISNULL(SUM(ROUND(ISNULL(T1.IM_CARG_MESE, 0),2)), 0), 
      	ABONO_SOLES = ISNULL(SUM(ROUND(ISNULL(T1.IM_ABON_MESE, 0),2)), 0)
     From TMPLAN_CORP T2
       Left Outer Join TASALD_CORP T1
	   On T1.NU_ANNO	= @INNU_ANNO 
	   And T1.NU_MESE	= @INNU_MESE 
	   And T1.CO_CNTA_CORP = T2.CO_CNTA_CORP 
	   And T1.TI_AUXI_CORP IS NULL
     Where DATALENGTH(RTRIM(T2.CO_CNTA_CORP)) = @INNU_DGTO 
     Group by T2.CO_CNTA_CORP
End 

   Open CU_TASALD_CORP_Q01_1
   Fetch CU_TASALD_CORP_Q01_1 into @CSCO_CNTA, @CSNO_CNTA, @CNIM_DEBE, @CNIM_HABE
     While @@fetch_status = 0
      begin

       Select @VNIM_DEBE_ACUM = 0
       Select @VNIM_HABE_ACUM = 0

       Select @VNIM_DEBE_ACUM = ISNULL(SUM(ROUND(ISNULL(T1.IM_CARG_MESE, 0),2)), 0), 
               @VNIM_HABE_ACUM= ISNULL(SUM(ROUND(ISNULL(T1.IM_ABON_MESE, 0),2)), 0)
       From  TASALD_CORP T1, TMPLAN_CORP T2
       Where T1.NU_ANNO	= @INNU_ANNO and T1.NU_MESE Between 0 and @INNU_MESE
         	  and T1.CO_CNTA_CORP = RTRIM(@CSCO_CNTA)
	  and T1.TI_AUXI_CORP IS NULL
	  and T1.CO_CNTA_CORP = T2.CO_CNTA_CORP
       Group by T1.CO_CNTA_CORP

       Insert #TWSALD_CORP_Q01_1 (CO_CNTA_CORP, DE_CNTA_CORP, DEBE , HABER, 
	  	            DEBE_ACUM, HABER_ACUM ) 
       Values (RTRIM(@CSCO_CNTA),RTRIM(@CSNO_CNTA),@CNIM_DEBE,@CNIM_HABE,@VNIM_DEBE_ACUM,
               @VNIM_HABE_ACUM)

       Fetch CU_TASALD_CORP_Q01_1 into @CSCO_CNTA, @CSNO_CNTA, @CNIM_DEBE, @CNIM_HABE
     end

Close CU_TASALD_CORP_Q01_1
Deallocate  CU_TASALD_CORP_Q01_1


If @ISFL_MONT = 'T'
Begin 

If @ISTI_PRES ='U'
Begin
Select CO_CNTA_CORP, DE_CNTA_CORP, DEBE, HABER, DEBE_ACUM, HABER_ACUM,NULL,NULL,NULL,NULL,1, 0
From #TWSALD_CORP_Q01_1
UNION ALL
Select T2.CO_CNTA_CORP, T2.DE_CNTA_CORP,NULL,NULL,NULL,NULL,ISNULL(SUM(ISNULL(T1.DEBE,0)),0),ISNULL(SUM(ISNULL(T1.HABER,0)),0),ISNULL(SUM(ISNULL(T1.DEBE_ACUM,0)),0),ISNULL(SUM(ISNULL(T1.HABER_ACUM,0)),0), 0, 1
FROM #TWSALD_CORP_Q01_1 T1, TMPLAN_CORP T2
WHERE DATALENGTH(T2.CO_CNTA_CORP) < @INNU_DGTO
AND T1.CO_CNTA_CORP LIKE  T2.CO_CNTA_CORP + '%'
GROUP BY T2.CO_CNTA_CORP, T2.DE_CNTA_CORP
Order by 1
End

If @ISTI_PRES ='E'
Begin
Select CO_CNTA_CORP, DE_CNTA_CORP, ROUND(DEBE/1000,2), ROUND(HABER/1000,2), ROUND(DEBE_ACUM/1000,2), ROUND(HABER_ACUM/1000,2),NULL,NULL,NULL,NULL, 1, 0
From #TWSALD_CORP_Q01_1
UNION ALL
Select T2.CO_CNTA_CORP, T2.DE_CNTA_CORP,NULL,NULL,NULL,NULL,ISNULL(SUM(ROUND(ISNULL(T1.DEBE,0)/1000,2)),0),ISNULL(SUM(ROUND(ISNULL(T1.HABER,0)/1000,2)),0),ISNULL(SUM(ROUND(ISNULL(T1.DEBE_ACUM,0)/1000,2)),0),ISNULL(SUM(ROUND(ISNULL(T1.HABER_ACUM,0)/1000,2)),0), 0, 1
FROM #TWSALD_CORP_Q01_1 T1, TMPLAN_CORP T2
WHERE
DATALENGTH(T2.CO_CNTA_CORP) < @INNU_DGTO
AND T1.CO_CNTA_CORP LIKE  T2.CO_CNTA_CORP + '%'
GROUP BY T2.CO_CNTA_CORP, T2.DE_CNTA_CORP
Order by 1
End

If @ISTI_PRES ='O'
Begin
Select CO_CNTA_CORP, DE_CNTA_CORP, ROUND(DEBE/1000000,2), ROUND(HABER/1000000,2), ROUND(DEBE_ACUM/1000000,2), ROUND(HABER_ACUM/1000000,2),NULL,NULL,NULL,NULL, 
1, 0
From #TWSALD_CORP_Q01_1
UNION ALL
Select T2.CO_CNTA_CORP, T2.DE_CNTA_CORP,NULL,NULL,NULL,NULL,ISNULL(SUM(ROUND(ISNULL(T1.DEBE,0)/1000000,2)),0),ISNULL(SUM(ROUND(ISNULL(T1.HABER,0)/1000000,2)),0),ISNULL(SUM(ROUND(ISNULL(T1.DEBE_ACUM,0)/1000000,2)),0),ISNULL(SUM(ROUND(ISNULL(T1.HABER_ACUM,0)/1000000,2)),0), 0, 1
FROM #TWSALD_CORP_Q01_1 T1, TMPLAN_CORP T2
WHERE
DATALENGTH(T2.CO_CNTA_CORP) < @INNU_DGTO
AND T1.CO_CNTA_CORP LIKE  T2.CO_CNTA_CORP + '%'
GROUP BY T2.CO_CNTA_CORP, T2.DE_CNTA_CORP
Order by 1
End
End /*--FIN SI ES TODO--*/

If @ISFL_MONT = 'M'
Begin 

If @ISTI_PRES ='U'
Begin
Select CO_CNTA_CORP, DE_CNTA_CORP, DEBE, HABER, DEBE_ACUM, HABER_ACUM,NULL,NULL,NULL,NULL, 1, 0
From #TWSALD_CORP_Q01_1
Where Abs(DEBE) > 0 Or Abs(HABER) > 0 Or Abs(DEBE_ACUM) > 0 Or Abs(HABER_ACUM) > 0 
UNION ALL
Select T2.CO_CNTA_CORP, T2.DE_CNTA_CORP,NULL,NULL,NULL,NULL,ISNULL(SUM(ISNULL(T1.DEBE,0)),0),ISNULL(SUM(ISNULL(T1.HABER,0)),0),ISNULL(SUM(ISNULL(T1.DEBE_ACUM,0)),0),ISNULL(SUM(ISNULL(T1.HABER_ACUM,0)),0), 0, 1
FROM #TWSALD_CORP_Q01_1 T1, TMPLAN_CORP T2
WHERE
DATALENGTH(T2.CO_CNTA_CORP) < @INNU_DGTO
AND T1.CO_CNTA_CORP LIKE  T2.CO_CNTA_CORP + '%'
And (Abs(t1.DEBE) > 0 Or Abs(t1.HABER) > 0 Or Abs(t1.DEBE_ACUM) > 0 Or Abs(t1.HABER_ACUM) > 0) 
GROUP BY T2.CO_CNTA_CORP, T2.DE_CNTA_CORP
Order by 1
End

If @ISTI_PRES ='E'
Begin
Select CO_CNTA_CORP, DE_CNTA_CORP, ROUND(DEBE/1000,2), ROUND(HABER/1000,2), ROUND(DEBE_ACUM/1000,2), ROUND(HABER_ACUM/1000,2),NULL,NULL,NULL,NULL, 1, 0
From #TWSALD_CORP_Q01_1
Where Abs(DEBE) > 0 Or Abs(HABER) > 0 Or Abs(DEBE_ACUM) > 0 Or Abs(HABER_ACUM) > 0 
UNION ALL
Select T2.CO_CNTA_CORP, T2.DE_CNTA_CORP,NULL,NULL,NULL,NULL,ISNULL(SUM(ROUND(ISNULL(T1.DEBE,0)/1000,2)),0),ISNULL(SUM(ROUND(ISNULL(T1.HABER,0)/1000,2)),0),ISNULL(SUM(ROUND(ISNULL(T1.DEBE_ACUM,0)/1000,2)),0),ISNULL(SUM(ROUND(ISNULL(T1.HABER_ACUM,0)/1000,2)),0), 0, 1
FROM #TWSALD_CORP_Q01_1 T1, TMPLAN_CORP T2
WHERE
DATALENGTH(T2.CO_CNTA_CORP) < @INNU_DGTO
AND T1.CO_CNTA_CORP LIKE  T2.CO_CNTA_CORP + '%'
And (Abs(t1.DEBE) > 0 Or Abs(t1.HABER) > 0 Or Abs(t1.DEBE_ACUM) > 0 Or Abs(t1.HABER_ACUM) > 0) 
GROUP BY T2.CO_CNTA_CORP, T2.DE_CNTA_CORP
Order by 1
End

If @ISTI_PRES ='O'
Begin
Select CO_CNTA_CORP, DE_CNTA_CORP, ROUND(DEBE/1000000,2), ROUND(HABER/1000000,2), ROUND(DEBE_ACUM/1000000,2), ROUND(HABER_ACUM/1000000,2),NULL,NULL,NULL,NULL, 1, 0
From #TWSALD_CORP_Q01_1
Where Abs(DEBE) > 0 Or Abs(HABER) > 0 Or Abs(DEBE_ACUM) > 0 Or Abs(HABER_ACUM) > 0 
UNION ALL
Select T2.CO_CNTA_CORP, T2.DE_CNTA_CORP,NULL,NULL,NULL,NULL,ISNULL(SUM(ROUND(ISNULL(T1.DEBE,0)/1000000,2)),0),ISNULL(SUM(ROUND(ISNULL(T1.HABER,0)/1000000,2)),0),ISNULL(SUM(ROUND(ISNULL(T1.DEBE_ACUM,0)/1000000,2)),0),ISNULL(SUM(ROUND(ISNULL(T1.HABER_ACUM,0)/1000000,2)),0), 0, 1
FROM #TWSALD_CORP_Q01_1 T1, TMPLAN_CORP T2
WHERE
DATALENGTH(T2.CO_CNTA_CORP) < @INNU_DGTO
AND T1.CO_CNTA_CORP LIKE  T2.CO_CNTA_CORP + '%'
And (Abs(t1.DEBE) > 0 Or Abs(t1.HABER) > 0 Or Abs(t1.DEBE_ACUM) > 0 Or Abs(t1.HABER_ACUM) > 0) 
GROUP BY T2.CO_CNTA_CORP, T2.DE_CNTA_CORP
Order by 1
End
End /*--FIN SI ES TODO--*/

return
/*--------------------------------- Fin ---------------------------------------*/


















GO
